package DBAccess;
import java.util.Date;
import java.sql.*;

public class DBAccess {
	public static Connection con = null;


    public DBAccess(){
    	String url = "jdbc:mysql://localhost:8889/"; //8889 for mike, 3306 for everyone else
    	String dbName = "cs410";
    	String driver = "com.mysql.jdbc.Driver";
    	String userName = "root";
    	String password = "root";
    	String os = System.getProperty("os.name").toLowerCase();
    	boolean isWindows = (os.indexOf( "win" ) >= 0);
        url = isWindows?"jdbc:mysql://localhost:3306/":"jdbc:mysql://localhost:8889/";
		try{	
			if(con==null){
				Class.forName(driver).newInstance();
				con = DriverManager.getConnection(url + dbName, userName, password);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
    
    }
    
    public static void close(Statement st, ResultSet rs){
		if (st!=null)
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		if (rs!=null)
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    }
	/** AccountDB Functions **/
	public static int insertUser(String password, String email, String name) {
		PreparedStatement ps;
		int userID=-1;

		try {
			ps = con.prepareStatement("INSERT INTO accountdb (pword, email, name) VALUES ('"+password+"' , '"+email+"' , '"+name+"')");
			ps.executeUpdate();
			ResultSet rs = null;
			rs = ps.getGeneratedKeys();
			if (rs.next())
				userID = rs.getInt(1);
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return userID;
	}

//	public static int updatePassword(String newPassword, int userID) {
//		PreparedStatement ps;
//
//		try {
//			ps = con.prepareStatement("UPDATE accountdb SET Password = ? WHERE userID = ?");
//
//			ps.setString(1, newPassword);
//			ps.setInt(2, userID);
//			ps.executeUpdate();
//
//			ps.close();
//		} catch (SQLException ex) {
//			System.out.println("Message: " + ex.getMessage());
//
//			try {
//				con.rollback();
//			} catch (SQLException ex2) {
//				System.out.println("Message: " + ex2.getMessage());
//				return -1;
//			}
//			return -2;
//		}
//		return 0;
//	}

	
	public static int updateEmail(String email, int userID) {
		PreparedStatement ps;

		try {
			ps = con.prepareStatement("UPDATE accountdb SET email = ? WHERE userID = ?");

			ps.setString(1, email);
			ps.setInt(2, userID);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

	
	public static int updateName(String name, int userID) {
		PreparedStatement ps;

		try {
			ps = con.prepareStatement("UPDATE accountdb SET name = ? WHERE userID = ?");

			ps.setString(1, name);
			ps.setInt(2, userID);
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

	/**MatchmakingDB functions**/
	public static int insertMatch(int userID, int otherID, int score) {
		PreparedStatement ps;

		try {
			ps = con.prepareStatement("INSERT INTO matchmakingdb Values ("+userID+","+otherID+"," +score+")");
			ps.executeUpdate();


			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				System.exit(-1);
				return -1;
			}
			return -2;
		}
		return 0;
	}

	public static int updateMatch(int matchID, int score) {
		PreparedStatement ps;
		try {
			ps = con.prepareStatement("UPDATE matchmakingdb SET score=? WHERE matchID = ?");
			ps.setInt(1, score);
			ps.setInt(2, matchID);
			ps.executeUpdate();
			
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				System.exit(-1);
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	/**InboxDB functions**/
	public static int insertMessage(int fromid, int toid, String message) {
		PreparedStatement ps;

		try {
			ps = con.prepareStatement("INSERT INTO inboxdb (fromID, toID,fromDeleteFlag, toDeleteflag, message, messageTime)" +
					" Values (" +fromid+","+toid+ "," +0+","+ 0+", '"+message+"',NOW())");
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}


	//where deleting a message sets the user's "
	public static int deleteMessage(int messageID, boolean fromDeleteFlag, boolean toDeleteFlag) {
		PreparedStatement ps;
		try {
			ps = con.prepareStatement("UPDATE inboxdb SET fromDeleteFlag = ?, toDeleteFlag=? WHERE messageID = ?");
			ps.setBoolean(1, fromDeleteFlag);
			ps.setBoolean(2, toDeleteFlag);
			ps.setInt(3, messageID);
			ps.executeUpdate();
			
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

	
	/**OtherUserDB functons**/
	public static int insertOtherUser(int userID, int otherUserID, int friendblocked, String group){
		PreparedStatement ps;
		String insertString;
		if (group==null)
			insertString = "INSERT INTO otherusersdb Values ("+userID+"," +otherUserID+","+friendblocked+ ", NULL)" ;
		else
			insertString ="INSERT INTO otherusersdb Values ("+userID+"," +otherUserID+","+friendblocked+ ",'" +group+"')";

		try {
			ps = con.prepareStatement(insertString);
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

	public static int updateOtherUser(int userID, int otherUserID, int friendblocked, String group){
		PreparedStatement ps;
		try {
			ps = con.prepareStatement("UPDATE otherusersdb SET friendblocked = ?, fgroup = ? WHERE userID = ? AND otherUserID = ?");

			ps.setInt(1, friendblocked);
			if (group==null)
				ps.setString(2, "");
			else
				ps.setString(2, group);
			
			ps.setInt(3, userID);
			ps.setInt(4, otherUserID);
			ps.execute();
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	/**PersonalDB Functions**/
	public static int insertAccount(int userID){
		PreparedStatement ps;
		String insertString;
		insertString = "INSERT INTO personaldb (userID) Values ("+userID+")";
		
		try {
			ps = con.prepareStatement(insertString);
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	public static int insertFlirt(int userID, int flirtFrom){
		PreparedStatement ps;
		String insertString;
		insertString = "INSERT INTO flirtdb (userID, flirtFrom, flirtTime) VALUES(?, ?, NOW())";
		
		try {
			ps = con.prepareStatement(insertString);
			ps.setInt(1, userID);
			ps.setInt(2, flirtFrom);
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	/**notificationsdb functions**/
	public static int insertNotification(int userID, String message){
		PreparedStatement ps;
		String insertString = "INSERT INTO notificationsdb (userID, message, time) VALUES(?, ?, NOW())";		
//		String updateString = "UPDATE personaldb set notificationflag=1 where userid=?";		
		
		try {
			ps = con.prepareStatement(insertString);
			ps.setInt(1, userID);
			ps.setString(2, message);
			ps.executeUpdate();
//			ps.close();
//			ps = con.prepareStatement(updateString);
//			ps.setInt(1, userID);
//			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	public static int insertNotification(int userID, String message, int fromid){
		PreparedStatement ps;
		String insertString = "INSERT INTO notificationsdb (userID, message, time, fromid) VALUES(?, ?, NOW(), ?)";		
//		String updateString = "UPDATE personaldb set notificationflag=1 where userid=?";		
		
		try {
			ps = con.prepareStatement(insertString);
			ps.setInt(1, userID);
			ps.setString(2, message);
			ps.setInt(3, fromid);
			ps.executeUpdate();
//			ps.close();
//			ps = con.prepareStatement(updateString);
//			ps.setInt(1, userID);
//			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	public static int updateNotification(int userID){
		PreparedStatement ps;
		String updateString = "UPDATE notificationsdb set checked=1 where userid=?";
		
		try {
			ps = con.prepareStatement(updateString);
			ps.setInt(1, userID);
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	/**
	 * update a row in the personalDB table
	 * @param userID - the userID to update
	 * @param profilePic - set to null to ignore
	 * @param address - set to null to ignore
	 * @param height - set to negative to ignore
	 * @param weight - set to negative to ignore
	 * @param birthday - set to null to ignore
	 * @param interests - set to null to ignore
	 * @param sex - set to negative to ignore
	 * @param orientation - set to negative to ignore
	 * @param newsletterFlag - set to negative to ignore
	 * @param notificationFlag - set to negative to ignore
	 */
	public static int updateInfo(int userID,  String address, int height, int weight, int byear, int bmonth, int bdate,
		String interests, int sex, int orientation, int newsletterFlag, int notificationFlag, String ethnicity, String religion){
		PreparedStatement ps;
		try {
			String update="UPDATE personaldb SET ";
			if (address!=null)
				update += "address='"+address+"',";
			if (height>=0)
				update += "height="+height+",";
			if (weight>=0)
				update += "weight="+weight+",";
			if (byear >=0 && bmonth >=0 && bdate >=0){
				String birthdateString = byear+"/"+bmonth+"/"+bdate;
				System.out.println(birthdateString);
				update += "birthdate='"+birthdateString+"',";
				Date now = new Date();
				int age = now.getYear()-byear;
				if(now.getMonth()+1<bmonth)
					age--;
				else if(now.getMonth()+1==bmonth && now.getDate() < bdate)
					age--;
				update += "age="+age+",";
			}
			if (interests!=null)
				update += "interests='"+interests+"',";
			if (sex>=0)
				update += "sex="+sex+",";
			if (orientation>=0)
				update += "orientation="+orientation+",";
			if (newsletterFlag>=0)
				update += "newsletterFlag="+newsletterFlag+",";
			if (notificationFlag>=0)
				update += "notificationFlag="+notificationFlag+",";
			if (religion!=null)
				update += "religion='"+religion+"',";
			if (ethnicity!=null)
				update += "ethnicity='"+ethnicity+"',";
			update+=",";
			update = update.replace(",,", " ");
			update+="WHERE userID="+userID;
			
			ps = con.prepareStatement(update);
			ps.executeUpdate();
			
			System.out.println(ps.toString());
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	
	/**preferenceDB Functions**/
	public static int insertPreference(int userID, int wl, int wu, 
									int hl, int hu, int al, int au, 
									String ethnicity, String religion, 
									int gender)
	{
		PreparedStatement ps;
		String insertString;
		insertString = "INSERT INTO preferencedb VALUES (userid=?," +
				" weightlower=?, weightupper=?, heightlower=?, heightupper=?," +
				" agelower=?, ageupper=?, ethnicity=?, religion=?, gender=?)";
		insertString = "INSERT INTO preferencedb VALUES (?,?,?,?,?,?,?,?,?,?)";
		
		try {
			ps = con.prepareStatement(insertString);
			ps.setInt(1, userID);
			ps.setInt(2, wl);
			ps.setInt(3, wu);
			ps.setInt(4, hl);
			ps.setInt(5, hu);
			ps.setInt(6, al);
			ps.setInt(7, au);
			ps.setString(8,ethnicity);
			ps.setString(9, religion);
			ps.setInt(10, gender);
			System.out.println(ps.toString());
			ps.executeUpdate();

			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	/**WallDB Functions**/
	public static int insertPost(int userID, int fromID, String message, int parentID){
		PreparedStatement ps;
		String insertString;
		insertString = "INSERT INTO walldb (userID, fromID, message, parentID, time) "+ 
				"Values ("+userID+", "+fromID+", '"+message+"', "+parentID+", NOW())";
		System.out.println(insertString);
		try {
			ps = con.prepareStatement(insertString);
			ps.executeUpdate();


			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			try {
				// undo the insert
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

	public static int updateWall(int postID, int deleteFlag){
		PreparedStatement ps;
		try {
			String update="UPDATE walldb SET deleteFlag = ? WHERE postID = ?";
			ps = con.prepareStatement(update);
			ps.setInt(1, deleteFlag);
			ps.setInt(2, postID);
			ps.executeUpdate();
			
			System.out.println(ps.toString());
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}
	public static int removeOtherUser(int userID, int otherID) {
		PreparedStatement ps;
		try {
			String update="DELETE FROM otherusersdb WHERE userID = ? AND otherUserID = ?";
			ps = con.prepareStatement(update);
			ps.setInt(1, userID);
			ps.setInt(2, otherID);
			ps.executeUpdate();
			
			System.out.println(ps.toString());
			ps.close();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());

			try {
				con.rollback();
			} catch (SQLException ex2) {
				System.out.println("Message: " + ex2.getMessage());
				return -1;
			}
			return -2;
		}
		return 0;
	}

}
